<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Getting database metadata</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="C#, SQLite, ADO.NET, database, metadata, tutorial, learn SQLite C# ">
<meta name="description" content="In this part of the SQLite C# tutorial, we work with database metadata.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">

<div>
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

</div>


<div class="content2">

<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Getting database metadata</h1>

<p>
Metadata is information about the data in the database. 
Metadata in SQLite contains information about the tables 
and columns, in which we store data. Number of rows affected 
by an SQL statement is metadata. Number of rows and columns returned 
in a result set belong to metadata as well.
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
Metadata in SQLite can be obtained using the PRAGMA command. 
SQLite objects may have attributes, which are metadata. Finally, 
we can also obtain specific metatada from querying the SQLite 
system sqlite_master table.
</p>


<pre class="code">
using System;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {
        string cs = "URI=file:test.db";

        string nrows = null;

        try {
            Console.Write("Enter rows to fetch: ");
            nrows = Console.ReadLine();
        } catch (FormatException e)
        {
            Console.WriteLine(e.ToString());
        }

        using (SqliteConnection con = new SqliteConnection(cs))
        {
            con.Open();

            using (SqliteCommand cmd = con.CreateCommand())
            {

                cmd.CommandText = "SELECT * FROM Cars LIMIT @Id";
                cmd.Prepare();

                cmd.Parameters.AddWithValue("@Id", Int32.Parse(nrows));

                int cols = 0;
                int rows = 0;

                using (SqliteDataReader rdr = cmd.ExecuteReader())
                {

                    cols = rdr.FieldCount;
                    rows = 0;

                    while (rdr.Read()) 
                    {
                        rows++;
                    }

                    Console.WriteLine("The query fetched {0} rows", rows);
                    Console.WriteLine("Each row has {0} cols", cols);
                }    
            }

            con.Close();
        }
    }
}
</pre>

<p>
In the above example, we get the number of rows and columns returned
by a query. 
</p>

<pre class="explanation">
try {
    Console.Write("Enter rows to fetch: ");
    nrows = Console.ReadLine();
} catch (FormatException e)
{
    Console.WriteLine(e.ToString());
}
</pre>

<p>
The example asks for the number of rows on the command line.
</p>

<pre class="explanation">
cmd.CommandText = "SELECT * FROM Cars LIMIT @Id";
cmd.Prepare();

cmd.Parameters.AddWithValue("@Id", Int32.Parse(nrows));
</pre>

<p>
We limit the selected rows to the number provided to the program.
</p>

<pre class="explanation">
cols = rdr.FieldCount;
</pre>

<p>
The number of returned columns can be easily get from the 
<code>FieldCount</code> property of the <code>SqliteDataReader</code>
object. 
</p>

<pre class="explanation">
while (rdr.Read()) 
{
    rows++;
}
</pre>

<p>
We count the number of rows in the result set. 
</p>

<pre>
$ mono fields_rows.exe 
Enter rows to fetch: 4
The query fetched 4 rows
Each row has 3 cols
</pre>

<p>
Output.
</p>


<h2>Column headers</h2>

<p>
Next we will show, how to print column headers with the data
from a database table.
</p>

<pre class="code">
using System;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {

        string cs = "URI=file:test.db";

        using (SqliteConnection con = new SqliteConnection(cs))
        {
            con.Open();

            string stm = "SELECT * FROM Cars LIMIT 5";

            using (SqliteCommand cmd = new SqliteCommand(stm, con))
            {

                using (SqliteDataReader rdr = cmd.ExecuteReader())
                {
                    Console.WriteLine(String.Format("{0, -3} {1, -8} {2, 8}", 
                        rdr.GetName(0), rdr.GetName(1), rdr.GetName(2)));

                    while (rdr.Read()) 
                    {
                        Console.WriteLine(String.Format("{0, -3} {1, -8} {2, 8}", 
                            rdr.GetInt32(0), rdr.GetString(1), rdr.GetInt32(2)));
                    }
                }
            }

            con.Close();
        }
    }
}
</pre>

<p>
In this program, we select 5 rows from the Cars table
with their column names. 
</p>

<pre class="explanation">
using (SqliteDataReader rdr = cmd.ExecuteReader())
</pre>

<p>
We create a <code>SqliteDataReader</code> object. 
</p>


<pre class="explanation">
Console.WriteLine(String.Format("{0, -3} {1, -8} {2, 8}", 
    rdr.GetName(0), rdr.GetName(1), rdr.GetName(2)));
</pre>

<p>
We get the names of the columns with the <code>GetName()</code> 
method of the reader. The <code>String.Format()</code> method is
used to format the data. 
</p>

<pre class="explanation">
while (rdr.Read()) 
{
    Console.WriteLine(String.Format("{0, -3} {1, -8} {2, 8}", 
        rdr.GetInt32(0), rdr.GetString(1), rdr.GetInt32(2)));
}       
</pre>

<p>
We print the data that was returned by the SQL statement
to the terminal. 
</p>


<pre>
$ dmcs columns.cs -r:Mono.Data.Sqlite.dll
$ mono columns.exe 
Id  Name        Price
1   Audi        52642
2   Mercedes    57127
3   Skoda        9000
4   Volvo       29000
5   Bentley    350000
</pre>

<p>
Ouput of the program. 
</p>


<h2>Affected rows</h2>

<p>
In the following example, we will find out how many changes have
been done by a particular SQL command.
</p>

<pre class="code">
using System;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {
        string cs = "Data Source=:memory:";

        using (SqliteConnection con = new SqliteConnection(cs))
        {

            con.Open();

            using (SqliteCommand cmd = new SqliteCommand(con))
            {
                cmd.CommandText = "CREATE TABLE Friends(Id INT, Name TEXT)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Friends VALUES(1, 'Tom')";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Friends VALUES(2, 'Jane')";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Friends VALUES(3, 'Rebekka')";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Friends VALUES(4, 'Lucy')";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Friends VALUES(5, 'Robert')";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "DELETE FROM Friends WHERE Id IN (3, 4, 5)";
                int n = cmd.ExecuteNonQuery();

                Console.WriteLine("The statement has affected {0} rows", n);
                
           }

           con.Close();
        }
    }
}
</pre>

<p>
We create a Friends table in memory. In the last SQL command, we delete three
rows. The <code>ExecuteNonQuery()</code> method returns the number of 
rows affected by the last SQL command.
</p>

<pre class="explanation">
cmd.CommandText = "DELETE FROM Friends WHERE Id IN (3, 4, 5)";
</pre>

<p>
In this SQL statement, we delete three rows. 
</p>

<pre class="explanation">
int n = cmd.ExecuteNonQuery();
</pre>

<p>
We find out the number of changes done by the last SQL
statement. 
</p>

<pre>
$ mono affected.exe 
The statement has affected 3 rows
</pre>

<p>
Example output.
</p>


<h2>Table schema</h2>

<p>
There is a <code>GetSchemaTable()</code> method which returns metadata 
about each column. It returns many values, among others the column
name, column size, the base table name or whether the column is unique
or not. 
</p>

<pre class="code">
using System;
using System.Data;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {
        string cs = "URI=file:test.db";

        using(SqliteConnection con = new SqliteConnection(cs)) 
        {
            con.Open();

            string stm = "SELECT * FROM Cars LIMIT 3";   

            using (SqliteCommand cmd = new SqliteCommand(stm, con))
            {

                using (SqliteDataReader rdr = cmd.ExecuteReader())
                {
                    DataTable schemaTable = rdr.GetSchemaTable();

                    foreach (DataRow row in schemaTable.Rows)
                    {
                        foreach (DataColumn col in schemaTable.Columns)
                            Console.WriteLine(col.ColumnName + " = " + row[col]);
                        Console.WriteLine();
                    }
                }
            }

            con.Close();
         }
    }
}
</pre>

<p>
The example prints lots of metadata about table columns. 
</p>

<pre class="explanation">
DataTable schemaTable = rdr.GetSchemaTable();
</pre>

<p>
We get the schema table. 
</p>

<pre class="explanation">
foreach (DataRow row in schemaTable.Rows)
{
    foreach (DataColumn col in schemaTable.Columns)
        Console.WriteLine(col.ColumnName + " = " + row[col]);
    Console.WriteLine();
}
</pre>

<p>
We go through the schema table rows, which hold the metadata, and
print them to the console. 
</p>


<pre>
$ dmcs schema.cs -r:Mono.Data.Sqlite.dll -r:System.Data.dll
$ mono schema.exe 
ColumnName = Id
ColumnOrdinal = 0
ColumnSize = 8
NumericPrecision = 19
NumericScale = 0
IsUnique = True
IsKey = True
...
</pre>

<p>
Excerpt from the example output.
</p>



<h2>Table names</h2>

<p>
In our last example related to the metadata, we will 
list all tables in the test.db database. 
</p>

<pre class="code">
using System;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {
        string cs = "URI=file:test.db";

        using (SqliteConnection con = new SqliteConnection(cs))
        {
            con.Open();

            string stm = @"SELECT name FROM sqlite_master
                WHERE type='table' ORDER BY name";   

            using (SqliteCommand cmd = new SqliteCommand(stm, con))
            {
                using (SqliteDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read()) 
                    {
                        Console.WriteLine(rdr.GetString(0));
                    }
                }
            }    
            
            con.Close();    
        }
    }
}
</pre>

<p>
The code example prints all available tables in the chosen database
to the terminal. 
</p>

<pre class="explanation">
string stm = @"SELECT name FROM sqlite_master
    WHERE type='table' ORDER BY name"; 
</pre>

<p>
The table names are retrieved from the <code>sqlite_master</code> table.
</p>

<pre>
$ mono tables.exe 
Cars
Friends
Images
</pre>

<p>
These were the tables on our system. 
</p>

<p>
In this part of the SQLite C# tutorial, we have worked with database
metadata. 
</p>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified April 16, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

